﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Linq;
using System.Reflection;
using System.Web;

namespace Web.Dao
{

    public class DBConnectionsEngine
    {
        #region Attributes
        private System.Data.SqlClient.SqlConnection Conn = new System.Data.SqlClient.SqlConnection();
        private DataTable dt = new DataTable();
        private  System.Data.SqlClient.SqlDataAdapter da = new System.Data.SqlClient.SqlDataAdapter();
        private System.Data.SqlClient.SqlDataReader dr;
        private System.Data.SqlClient.SqlCommand cm;
        private System.Data.SqlClient.SqlTransaction tr;
        private ArrayList arlSqlCommand = new ArrayList();
        #endregion

        #region Constructor
        public DBConnectionsEngine()
        {
            this.Conn = new System.Data.SqlClient.SqlConnection();
            this.dt = new DataTable();
        }
        #endregion

        #region Execute Query

        public int ExecuteNonQuery(string pSql, System.Data.SqlClient.SqlConnection pConn)
        {
            int result = 0;
            try
            {
                if (pConn.State == ConnectionState.Open) pConn.Close();
                pConn.Open();
                //this.tr = pConn.BeginTransaction();
                this.cm = new System.Data.SqlClient.SqlCommand(pSql, pConn);
                result = this.cm.ExecuteNonQuery();
                //this.tr.Commit();
            }
            catch (System.Exception ex)
            {
                // new FileHandle().insertErrorLogFile(ex.ToString(), System.Reflection.MethodBase.GetCurrentMethod().Name);
                //this.tr.Rollback();
                throw new Exception();

            }
            finally
            {
                pConn.Close();
            }
            return result;
        }

        public int ExecuteNonQuery(string pSql, Hashtable h, System.Data.SqlClient.SqlConnection pConn)
        {
            int result = 0;
            try
            {
                if (pConn.State == ConnectionState.Open) pConn.Close();
                pConn.Open();
                //this.tr = pConn.BeginTransaction();
                this.cm = new System.Data.SqlClient.SqlCommand(pSql, pConn);
                // Hashtable
                foreach (string key in h.Keys)
                {
                    cm.Parameters.Add(key, h[key]);
                }
                //
                result = this.cm.ExecuteNonQuery();
                //this.tr.Commit();
            }
            catch (System.Exception ex)
            {
                // new FileHandle().insertErrorLogFile(ex.ToString(), System.Reflection.MethodBase.GetCurrentMethod().Name);
                //this.tr.Rollback();
                throw new Exception();

            }
            finally
            {
                pConn.Close();
            }
            return result;
        }

        public int ExecuteNonQuery(System.Data.SqlClient.SqlConnection pConn)
        {
            int result = 0;
            string command = "";
            try
            {
                if (pConn.State == ConnectionState.Open) pConn.Close();
                pConn.Open();

                this.cm = pConn.CreateCommand();
                //this.tr = pConn.BeginTransaction();

                if (this.arlSqlCommand.Count > 0)
                {
                    foreach (object item in this.arlSqlCommand)
                    {
                        command = item.ToString();
                        this.cm.CommandText = item.ToString();
                        result += this.cm.ExecuteNonQuery();
                    }
                    //this.tr.Commit();
                }

            }
            catch (System.Exception ex)
            {
                //new FileHandle().insertErrorLogFile(ex.ToString(), System.Reflection.MethodBase.GetCurrentMethod().Name);
                //this.tr.Rollback();
                throw new Exception();
            }
            finally
            {
                pConn.Close();
                pConn.Dispose();
                this.tr.Dispose();
                this.cm.Dispose();
                this.arlSqlCommand.Clear();
            }
            return result;
        }

        public object ExecuteScalar(string pSql, System.Data.SqlClient.SqlConnection pConn)
        {
            object countRecord = 0;
            try
            {
                if (pConn.State == ConnectionState.Open) pConn.Close();
                pConn.Open();

                this.cm = new System.Data.SqlClient.SqlCommand(pSql, pConn);
                countRecord = this.cm.ExecuteScalar();
            }
            catch (System.Exception ex)
            {
                // new FileHandle().insertErrorLogFile(ex.ToString(), System.Reflection.MethodBase.GetCurrentMethod().Name);
                //this.tr.Rollback();
                throw new Exception();
            }
            finally
            {
                pConn.Close();
            }
            return countRecord;
        }

        public System.Data.SqlClient.SqlCommand GetProcedureCommand(string pProcName, System.Data.SqlClient.SqlConnection pConn)
        {
            try
            {
                if (pConn.State == ConnectionState.Open) pConn.Close();
                pConn.Open();
                this.cm = new System.Data.SqlClient.SqlCommand(pProcName, pConn);
                this.cm.CommandType = CommandType.StoredProcedure;
                return this.cm;
            }
            catch (System.Exception ex)
            {
                // new FileHandle().insertErrorLogFile(ex.ToString(), System.Reflection.MethodBase.GetCurrentMethod().Name);
                this.tr.Rollback();
                throw new Exception();
            }
        }

        public System.Data.SqlClient.SqlCommand GetFunctionCommand(string pProcName, System.Data.SqlClient.SqlConnection pConn)
        {
            try
            {
                if (pConn.State == ConnectionState.Open) pConn.Close();
                pConn.Open();
                this.cm = new System.Data.SqlClient.SqlCommand(pProcName, pConn);
                this.cm.CommandType = CommandType.StoredProcedure;
                return this.cm;
            }
            catch (System.Exception ex)
            {
                // new FileHandle().insertErrorLogFile(ex.ToString(), System.Reflection.MethodBase.GetCurrentMethod().Name);
                this.tr.Rollback();
                throw new Exception();
            }
        }

        public int ManyExecuteNonQuery(System.Data.SqlClient.SqlConnection pConn, params string[] pSql)
        {
            int result = 0;
            try
            {
                if (pSql.Length > 0)
                {
                    if (pConn.State == ConnectionState.Open) pConn.Close();
                    pConn.Open();
                    this.tr = pConn.BeginTransaction();
                    for (int i = 0; i <= pSql.Length - 1; i++)
                    {
                        if (pSql[i] != null)
                        {
                            this.cm = new System.Data.SqlClient.SqlCommand(pSql[i], pConn);
                            result = result + this.cm.ExecuteNonQuery();
                        }
                    }
                    this.tr.Commit();
                }

            }
            catch (System.Exception ex)
            {
                result = 0;
                // new FileHandle().insertErrorLogFile(ex.ToString(), System.Reflection.MethodBase.GetCurrentMethod().Name);
                this.tr.Rollback();
                throw new Exception();
            }
            finally
            {
                pConn.Close();
            }
            return result;
        }

        public int ManyExecuteNonQuery(System.Data.SqlClient.SqlConnection pConn, ArrayList pSql)
        {

            int result = 0;
            try
            {

                if (pSql.Count > 0)
                {
                    if (pConn.State == ConnectionState.Open) pConn.Close();
                    pConn.Open();
                    //this.tr = pConn.BeginTransaction();
                    foreach (string tmpSql in pSql)
                    {

                        if (tmpSql != null)
                        {
                            this.cm = new System.Data.SqlClient.SqlCommand(tmpSql, pConn);
                            result = result + this.cm.ExecuteNonQuery();
                        }
                    }
                   // this.tr.Commit();
                }

            }
            catch (Exception e)
            {
                result = 0;
                // new FileHandle().insertErrorLogFile(e.ToString(), System.Reflection.MethodBase.GetCurrentMethod().Name);
                //this.tr.Rollback();
                throw new Exception();

            }
            finally
            {
                pConn.Close();
            }
            return result;
        }

        public DataTable GetDataTable(string pSql, System.Data.SqlClient.SqlConnection pConn)
        {
            this.dt = new DataTable();
            try
            {
                if (pConn.State == ConnectionState.Open) pConn.Close();
                pConn.Open();
                this.da = new System.Data.SqlClient.SqlDataAdapter(pSql, pConn);
                this.da.Fill(this.dt);
            }
            catch (Exception e)
            {
                // new FileHandle().insertErrorLogFile(e.ToString(), System.Reflection.MethodBase.GetCurrentMethod().Name);
                throw new Exception();
            }
            finally
            {
                pConn.Close();
            }
            return this.dt;
        }

        public DataTable GetDataTable(string strSql, Hashtable h, System.Data.SqlClient.SqlConnection Conn)
        {
            System.Data.SqlClient.SqlCommand cmd = Conn.CreateCommand();
            DataTable dt = new DataTable();
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = strSql;
           // cmd.BindByName = true;
            try
            {
                foreach (string key in h.Keys)
                {
                    cmd.Parameters.Add(key, h[key]);
                }
                System.Data.SqlClient.SqlDataAdapter adap = new System.Data.SqlClient.SqlDataAdapter(cmd);
                adap.Fill(dt);

            }
            catch (Exception e)
            {
                // new FileHandle().insertErrorLogFile(e.ToString(), System.Reflection.MethodBase.GetCurrentMethod().Name);
                throw new Exception();
            }
            return dt;
        }

        private static List<T> DataReaderMapToList<T>(DbDataReader dr)
        {
            List<T> lst = new List<T>();

            T obj = default(T);
            while (dr.Read())
            {
                obj = Activator.CreateInstance<T>();
                foreach (PropertyInfo prop in obj.GetType().GetProperties())
                {
                    try
                    {
                        if (!object.Equals(dr[prop.Name], DBNull.Value))
                        {
                            prop.SetValue(obj, dr[prop.Name], null);
                        }
                    }
                    catch (System.Exception ex)
                    {
                        prop.SetValue(obj, null, null);
                    }
                }
                lst.Add(obj);

            }

            return lst;
        }

        public T ExecuteSingle<T>(string strSql, Hashtable h, System.Data.SqlClient.SqlConnection Conn) where T : new()
        {
            T result = default(T);
            using (DbDataReader rdr = GetDataReader(strSql, h, Conn))
            {
                IEnumerable<T> items = DataReaderMapToList<T>(rdr).AsParallel();

                if (items.Count() > 0)
                    result = items.ElementAt(0);
            }
            return result;
        }

        public T ExecuteSingle<T>(string strSql, System.Data.SqlClient.SqlConnection Conn) where T : new()
        {
            T result = default(T);
            using (DbDataReader rdr = GetDataReader(strSql, Conn))
            {
                IEnumerable<T> items = DataReaderMapToList<T>(rdr).AsParallel();

                if (items.Count() > 0)
                    result = items.ElementAt(0);
            }
            return result;
        }

        public IEnumerable<T> ToList<T>(string strSql, Hashtable h, System.Data.SqlClient.SqlConnection Conn) where T : new()
        {
            IEnumerable<T> result;
            using (var rdr = GetDataReader(strSql, h, Conn))
                result = DataReaderMapToList<T>(rdr);

            return result;
        }

        public IEnumerable<T> ToList<T>(string strSql, System.Data.SqlClient.SqlConnection Conn) where T : new()
        {
            IEnumerable<T> result;
            using (var rdr = GetDataReader(strSql, Conn))
                result = DataReaderMapToList<T>(rdr);

            return result;
        }

        public System.Data.SqlClient.SqlDataReader GetDataReader(string pSql, System.Data.SqlClient.SqlConnection pConn)
        {
            try
            {
                if (pConn.State == ConnectionState.Open) pConn.Close();
                pConn.Open();

                this.cm = new System.Data.SqlClient.SqlCommand(pSql, pConn);
                this.dr = this.cm.ExecuteReader();
            }
            catch (Exception e)
            {
                //  new FileHandle().insertErrorLogFile(e.ToString(), System.Reflection.MethodBase.GetCurrentMethod().Name);
                throw new Exception();
            }
            return this.dr;
        }

        public DbDataReader GetDataReader(string pSql, Hashtable h,  System.Data.SqlClient.SqlConnection pConn)
        {
            try
            {
                if (pConn.State == ConnectionState.Open) pConn.Close();
                pConn.Open();
                // System.Data.SqlClient.SqlCommand cmd = Conn.CreateCommand();
                this.cm = new System.Data.SqlClient.SqlCommand(pSql, pConn);
                foreach (string key in h.Keys)
                {
                    cm.Parameters.Add(key, h[key]);
                }
                this.dr = this.cm.ExecuteReader();
            }
            catch (Exception e)
            {
                //  new FileHandle().insertErrorLogFile(e.ToString(), System.Reflection.MethodBase.GetCurrentMethod().Name);
                throw new Exception();
            }
            return this.dr;
        }

        #endregion
    }
}